using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class SearchPopUp : System.Web.UI.Page
{

    string controlid = "";
    string textboxid = "";
    string strBindRowID = "";
    string strBindafrowid = "";
    string strstoragevalue = "";
    string strdisplayvalue = "";
    int istoragefield;
    string searchdata = "";
    DataSet dsPgCtr = null;
    protected void Page_PreInit(object sender, EventArgs e)
    {
        this.Theme = Session["theme"].ToString();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        security hdds = new security();

        if (Session["userid"] == null) Response.Redirect(hdds.SessionExpiryURL);
        if (Session["userid"].ToString() == "") Response.Redirect(hdds.SessionExpiryURL);

        txtsearch.Focus();
        Page.Form.DefaultButton = btngo.ID;
        setPageTitle();
        DataSet dsBind = new DataSet();

        if (Request.QueryString["bnd"] != null)
            if (Request.QueryString["bnd"].ToString().Trim() != "")
                dsPgCtr = GetPageControlCaptions(Request.QueryString["bnd"].ToString());

        //if (Request.QueryString["PB"] != null) // Control Paramter Bind lookup for Report Designer and dashboard
        //{
        //    if (Request.QueryString["bnd"] != null)
        //        if (Request.QueryString["SD"] != null)
        //        {
        //            // Response.Write(GetParamterBindValue(Request.QueryString["bnd"].ToString(), Request.QueryString["SD"].ToString()));
        //            Response.Write("mytext");
        //        }
        //    return;
        //}

        if (HttpContext.Current.Request.QueryString["typ"] != null) // Checking for Lookup type (Drop down Extender control) or Compose Email
        {
            if (Request.QueryString["bnd"] != null)
            {
                strBindRowID = Request.QueryString["bnd"].ToString();
                controlid = Request.QueryString["CnID"].ToString();
                if (Request.QueryString["txtCnID"] != null)
                    textboxid = Request.QueryString["txtCnID"].ToString();

                if (!Page.IsPostBack)
                {
                    if (Request.QueryString["typ"].ToString() == "EM")
                        BuildGridForComposeEmail();
                    else
                        BuildSearch(true, false, true);
                }
            }
        }
        else
        {
            if (HttpContext.Current.Request.QueryString["isMas"] != null)
            {
                strBindRowID = Request.QueryString["bnd"].ToString();
                controlid = Request.QueryString["CnID"].ToString();
                textboxid = Request.QueryString["txtCnID"].ToString();
                if (!Page.IsPostBack)
                    if (Request.QueryString["SD"] != null)
                        txtsearch.Text = Request.QueryString["SD"].ToString();
                BuildGridForMasterDetail();
            }
            else
            {
                if (Request.QueryString["bnd"] != null)
                {
                    if (Request.QueryString["CnID"] != null)
                    {
                        strBindRowID = Request.QueryString["bnd"].ToString();
                        controlid = Request.QueryString["CnID"].ToString();
                        textboxid = Request.QueryString["txtCnID"].ToString();
                        //hfcontrolid.Value = Request.QueryString["CnID"].ToString();
                        //if(hfcontrolid.Value 
                        //searchdata = Request.QueryString["srv"].ToString();
                        //searchdata="javascript:getSearchValue('"+controlid+"');";
                        //txtsearch.Attributes.Add("onload", "javascript:getSearchValue('" + controlid + "');");
                        if (!Page.IsPostBack)
                        {
                            if (Request.QueryString["SD"] != null)
                                txtsearch.Text = Request.QueryString["SD"].ToString();
                            if (txtsearch.Text != "")
                                BuildSearch(true, false, false);
                            else
                                BuildSearch(true, true, false);
                        }
                    }
                }
            }
        }

    }

    private void BuildSearch(bool isSearchByValue, bool firsttime, bool isDDE)
    {
        DataSet dsBind = new DataSet();
        HDDatabase HDD = new HDDatabase();

        dsBind = getBindData(strBindRowID, isSearchByValue);
        //if(Session["user_afrowid"]!=null)
        //{
        //    strBindafrowid = HDD.GetColumnValue("select af_row_id from sy_bnd where af_row_id = '" + strBindRowID + "'");
        //    if(Session["user_afrowid"].ToString() !="e51e0327224a2eb9")
        //        if(strBindafrowid == "f496930dfe0b690f")
        //            dsBind =Removeafadminrecord(dsBind);    
        //}
        GenerateGrid(dsBind, firsttime, isDDE);
        HDD = null;
    }

    private void GenerateGrid(DataSet dsBind, bool firsttime, bool isDDE)
    {
        if (dsBind != null)
        {
            if (dsBind.Tables.Count > 0)
            {
                HtmlTable htresult = new HtmlTable();
                AddHeader(dsBind, ref htresult);
                AddRows(dsBind, ref htresult, firsttime, isDDE);
                tdsearch.Controls.Add(htresult);
            }
        }
    }
    private DataSet getBindData(string strBindRowID, bool isSearchByValue)
    {
        DataSet ds = new DataSet();
        BindControl BC = new BindControl();
        try
        {
            //strdisplayvalue = drr[0]["bind_text"].ToString();
            //strstoragevalue = drr[0]["bind_value"].ToString();
            ds = BC.getBindData(strBindRowID, isSearchByValue, txtsearch.Text, out strdisplayvalue, out strstoragevalue, "");
            //CommonFunctions.decodeData(ds);
        }
        catch (Exception ee) { }
        finally
        {
            BC = null;
        }
        //HDDatabase hdd = new HDDatabase();
        //BindControl BC = new BindControl();
        //DataSet ds = new DataSet();
        //try
        //{
        //    string sqlbind = "select * from sy_bnd where af_row_id = '" + strBindRowID + "'";
        //    DataTable dtBind = hdd.GetDataTable(sqlbind);
        //    if (dtBind != null)
        //    {
        //        if (dtBind.Rows.Count > 0)
        //        {
        //            DataRow[] drr = dtBind.Select();
        //            strdisplayvalue = drr[0]["bind_text"].ToString();
        //            strstoragevalue = drr[0]["bind_value"].ToString();
        //        }
        //    }
        //    string sql = "";
        //    if (isSearchByValue)
        //    {
        //        sql = BC.GetSearchSQLBySearchValue(strBindRowID,txtsearch.Text,true,true);
        //    }
        //    else
        //    {
        //        //sql = BC.GetBindSQLforSearch(strBindRowID);
        //        sql = BC.GetSearchSQLBySearchValue(strBindRowID, txtsearch.Text, false,true);
        //    }
        //    ds = hdd.GetDataset(sql);
        //    //foreach (DataColumn dc in ds.Tables[0].Columns)
        //    //{
        //    //    if (dc.ColumnName.Contains("@"))
        //    //    {
        //    //        string[] arrstorage = dc.ColumnName.Split('@');
        //    //        strstoragevalue = arrstorage[1].ToString();
        //    //        istoragefield = dc.Ordinal;
        //    //        ds.Tables[0].Columns[dc.ColumnName].ColumnName = strstoragevalue;
        //    //        ds.AcceptChanges();
        //    //    }
        //    //    if (dc.ColumnName.Contains("$"))
        //    //    {
        //    //        string[] arrdisplay = dc.ColumnName.Split('$');
        //    //        strdisplayvalue = arrdisplay[1].ToString();
        //    //        ds.Tables[0].Columns[dc.ColumnName].ColumnName = strdisplayvalue;
        //    //        ds.AcceptChanges();
        //    //    }
        //    //}
        //}
        //catch (Exception ex)
        //{
        //    LogWriter.WriteLog("Error : getBindData : "+ex.Message);
        //}
        //finally
        //{
        //    hdd = null;
        //    BC = null;
        //}
        return ds;
    }

    private void AddHeader(DataSet dsBind, ref HtmlTable htHeader)
    {
        //HtmlTable htHeader = new HtmlTable();
        htHeader.CellPadding = 0;
        htHeader.CellSpacing = 0;
        htHeader.Border = 0;
        htHeader.Width = "100%";
        HtmlTableRow htrheader = new HtmlTableRow();
        HtmlTableCell htcheader = new HtmlTableCell();
        htcheader.Attributes.Add("class", "pgForm_title");
        htrheader.Attributes.Add("class", "pgForm_title");
        foreach (DataColumn dc in dsBind.Tables[0].Columns)
        {
            //if (dc.Ordinal != istoragefield)
            if (dc.Ordinal != 0)
            {
                htcheader = new HtmlTableCell();
                Label lblheader = new Label();

                string caption = dc.Caption.Replace("_", " ");
                if (dsPgCtr != null)
                    if (dsPgCtr.Tables.Count > 0)
                        if (dsPgCtr.Tables[0].Rows.Count > 0)
                        {
                            DataRow[] drr = dsPgCtr.Tables[0].Select("control_attrib_id='" + dc.Caption + "'");
                            if (drr.Length > 0)
                                caption = drr[0]["control_name"].ToString();
                        }
                lblheader.Text = "&nbsp;" + HttpUtility.HtmlEncode(caption);
                lblheader.CssClass = "controlLabelFont";

                if (dc.ColumnName.Contains("+"))
                    lblheader.Text = "";

                htcheader.Controls.Add(lblheader);
                htcheader.Attributes.Add("class", "Grid_Header");
                htrheader.Controls.Add(htcheader);
            }
        }
        htHeader.Controls.Add(htrheader);
        // tdsearch.Controls.Add(htHeader);
    }
    private void AddRows(DataSet dsBind, ref HtmlTable htdata, bool firsttime, bool isDDE)
    {
        //HtmlTable htdata = new HtmlTable();
        htdata.CellPadding = 0;
        htdata.CellSpacing = 0;
        htdata.Border = 0;
        htdata.Width = "100%";
        HtmlTableRow htrdata = new HtmlTableRow();
        HtmlTableCell htcdata = new HtmlTableCell();
        int iClassCount = 0;
        bool status = AddNoOwnerRecord(ref htdata, firsttime);
        if (dsBind != null)
        {
            if (dsBind.Tables.Count > 0)
            {
                if (dsBind.Tables[0].Rows.Count > 0)
                {
                    //if (firsttime)
                    //{
                    //    AddemptyRow(ref htdata, ref htrdata, ref htcdata,dsBind);
                    //}
                    //else
                    //{
                    foreach (DataRow dr in dsBind.Tables[0].Rows)
                    {
                        htrdata = new HtmlTableRow();
                        for (int i = 0; i < dsBind.Tables[0].Columns.Count; i++)
                        {
                            //if (i != istoragefield)
                            if (i != 0)
                            {
                                htcdata = new HtmlTableCell();
                                htcdata.Height = "20px";
                                Literal literallink = new Literal();
                                //.Replace("\\", "\\\\")
                                //.Replace("\"", "\\\"")
                                string bindstoragevalue = dr["val"].ToString().Replace("\\", "~RDS~").Replace("'", "\\'").Replace("\"", "~RDQ~");
                                string binddisplayvalue = "";
                                try
                                {
                                    binddisplayvalue = dr[strdisplayvalue].ToString().Replace("\\", "~RDS~").Replace("'", "\\'").Replace("\"", "~RDQ~");
                                }
                                catch { binddisplayvalue = dr[1].ToString().Replace("\\", "~RDS~").Replace("'", "\\'").Replace("\"", "~RDQ~"); }
                                //literallink.Text = "<a href=\"#\" onclick=\"javascript:setvalue('" + controlid + "','"+textboxid+"','" + bindstoragevalue + "','" + binddisplayvalue + "'); closewindow(); return false;\" >" + dr[i].ToString() + "</a>";
                                string binddata = bindstoragevalue == "" ? "&nbsp;" : bindstoragevalue;
                                string displaydata = binddisplayvalue == "" ? "&nbsp;" : binddisplayvalue;
                                string text = dr[i].ToString() == "" ? "&nbsp" : HttpUtility.HtmlEncode(dr[i].ToString());
                                if (isDDE)
                                    literallink.Text = "<a href=\"#\" onclick=\"javascript:SetValueForDDE('" + controlid + "','" + binddata + "','" + displaydata + "'); closewindow(); return false;\" >" + text + "</a>";
                                else
                                    literallink.Text = "<a href=\"#\" onclick=\"javascript:setvalue('" + controlid + "','" + textboxid + "','" + binddata + "','" + displaydata + "'); closewindow(); return false;\" >" + text + "</a>";
                                htcdata.Controls.Add(literallink);
                                int iClass = iClassCount % 2;
                                htrdata.Attributes.Add("Class", "Grid_Row" + iClass.ToString());
                                htrdata.Controls.Add(htcdata);
                            }
                        }
                        htdata.Controls.Add(htrdata);
                        iClassCount++;
                    }
                    htcdata = new HtmlTableCell();
                    htrdata = new HtmlTableRow();
                    htcdata.Attributes.Add("class", "pgForm_title");
                    htrdata.Attributes.Add("class", "pgForm_title");
                    htcdata.Height = "20px";
                    htrdata.Height = "20px";
                    htcdata.ColSpan = dsBind.Tables[0].Columns.Count;
                    htrdata.Controls.Add(htcdata);
                    htdata.Controls.Add(htrdata);
                    // }
                }
                else
                {
                    if (!status)
                        AddemptyRow(ref htdata, ref htrdata, ref htcdata, dsBind);
                    else
                        AddFooter(dsBind, ref htdata);
                }
            }
        }
        //tdsearch.Controls.Add(htdata);
    }

    private bool AddNoOwnerRecord(ref HtmlTable ht, bool firsttime)
    {
        bool status = false;
        //if (!firsttime)
        //{
        if (txtsearch.Text == "" || "no owner".Contains(txtsearch.Text.ToLower().Trim()) || txtsearch.Text.Trim() == "*")
        {
            if (strBindafrowid == "f496930dfe0b690f")
            {
                int columncount = ht.Rows[0].Cells.Count;
                HtmlTableRow htr = new HtmlTableRow();
                for (int j = 0; j < columncount; j++)
                {
                    HtmlTableCell htc = new HtmlTableCell();
                    Literal literallink = new Literal();
                    literallink.Text = "<a href=\"#\" onclick=\"javascript:setvalue('" + controlid + "','" + textboxid + "','" + "" + "','" + "" + "'); closewindow(); return false;\" >" + "No Owner" + "</a>";
                    htc.Controls.Add(literallink);
                    htc.Height = "20px";
                    //htc.ColSpan = columncount;
                    htr.Controls.Add(htc);
                }
                htr.Attributes.Add("class", "Grid_Row1");
                ht.Controls.Add(htr);
                status = true;
            }
        }
        //}
        return status;
    }

    private void AddemptyRow(ref HtmlTable htdata, ref HtmlTableRow htrdata, ref HtmlTableCell htcdata, DataSet dsBind)
    {
        htrdata = new HtmlTableRow();
        htcdata = new HtmlTableCell();
        htcdata.Attributes.Add("class", "controlFont Grid_Row0");
        Label literalmsg = new Label();
        literalmsg.CssClass = "controlFont";
        literalmsg.Text = "No matches found";
        htcdata.Controls.Add(literalmsg);
        htcdata.ColSpan = dsBind.Tables[0].Columns.Count;
        htcdata.Width = "100%";
        htcdata.BorderColor = "red";
        //htcdata.Align = "center";
        htrdata.Controls.Add(htcdata);
        //for (int i = 0; i < dsBind.Tables[0].Columns.Count-2; i++)
        //{
        //    htcdata = new HtmlTableCell();
        //    htrdata.Cells.Add(htcdata);
        //}
        htdata.Controls.Add(htrdata);
        htcdata = new HtmlTableCell();
        htrdata = new HtmlTableRow();
        htcdata.Attributes.Add("class", "Dynamic_HtmlTableBottom");
        htrdata.Attributes.Add("class", "Grid_Footer");
        htcdata.Height = "20px";
        htrdata.Height = "20px";
        htcdata.ColSpan = dsBind.Tables[0].Columns.Count;
        htrdata.Controls.Add(htcdata);
        htdata.Controls.Add(htrdata);
    }

    private void AddFooter(DataSet dsBind, ref HtmlTable htdata)
    {
        HtmlTableCell htcdata = new HtmlTableCell();
        HtmlTableRow htrdata = new HtmlTableRow();
        htcdata.Attributes.Add("class", "pgForm_title");
        htrdata.Attributes.Add("class", "Grid_Footer");
        htcdata.Height = "20px";
        htrdata.Height = "20px";
        htcdata.ColSpan = dsBind.Tables[0].Columns.Count;
        htrdata.Controls.Add(htcdata);
        htdata.Controls.Add(htrdata);
    }


    private void setPageTitle()
    {
        try
        {
            if (Session["pageTitle"] != null)
            {
                Page.Header.Title = Session["pageTitle"].ToString();
            }
            else
            {
                if (System.IO.File.Exists(HttpContext.Current.Server.MapPath(".") + "\\maxbloxtitle.ini"))
                {
                    System.IO.StreamReader sr = new System.IO.StreamReader(HttpContext.Current.Server.MapPath(".") + "\\maxbloxtitle.ini");
                    Session["pageTitle"] = sr.ReadLine();
                    Page.Header.Title = Session["pageTitle"].ToString();
                    sr.Close();
                    sr = null;
                }
            }
            Page.Header.Title += " - Search";
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in setPageTitle function. Error: " + e.Message);
        }
    }


    protected void btngo_Click(object sender, EventArgs e)
    {
        if (Request.QueryString["typ"] != null)
        {
            if (Request.QueryString["typ"].ToString() == "EM")
                BuildGridForComposeEmail();
            else
                BuildSearch(true, false, true);
        }
        else
            BuildSearch(true, false, false);
    }

    private DataSet Removeafadminrecord(DataSet ds)
    {
        if (ds != null)
        {
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    int removeindex = -1;
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        if (ds.Tables[0].Rows[i]["val"].ToString() == "e51e0327224a2eb9")
                            removeindex = i;
                    }
                    if (removeindex > -1)
                    {
                        ds.Tables[0].Rows.RemoveAt(removeindex);
                        ds.AcceptChanges();
                    }
                }
            }
        }
        return ds;
    }

    private void BuildGridForMasterDetail()
    {
        DataSet ds = new DataSet();
        ds = GetDataSetforMasterDetail();
        if (ds.Tables.Count > 0)
            HandleControlDataType(ds.Tables[0]);
        if (ds != null)
        {
            if (!Page.IsPostBack)
            {
                if (txtsearch.Text.Trim() != "")
                    GenerateGrid(ds, false, false);
                else
                    GenerateGrid(ds, true, false);
            }
            else
                GenerateGrid(ds, false, false);
        }
    }

    private DataSet GetDataSetforMasterDetail()
    {
        DataSet ds = new DataSet();
        HDDatabase HDD = new HDDatabase();
        try
        {
            string sql = GetQueryforMasterDetail();
            if (sql != "")
                ds = HDD.GetDataset(sql);
        }
        catch
        { }
        finally
        {
            HDD = null;
        }
        return ds;
    }

    private string GetQueryforMasterDetail()
    {
        string sql = "";
        try
        {
            if (Request.QueryString["tbl"] != null)
            {
                if (Request.QueryString["fld"] != null)
                {
                    string tablename = Request.QueryString["tbl"].ToString();
                    string bindfield = Request.QueryString["fld"].ToString();
                    if (txtsearch.Text != "")
                    {
                        //sql = "select af_row_id as val," + bindfield + " as fld from " + tablename + " where "+bindfield+" like '%"+txtsearch.Text.Replace("'","''").Trim()+"%' ";
                        //sql = "select af_row_id as val," + bindfield + " from " + tablename + " where " + bindfield + " like '%" + txtsearch.Text.Replace("'", "''").Trim() + "%' ";
                        sql = "select af_row_id as val," + bindfield + " from " + tablename + " where " + bindfield + " like '%" + ConvertLocaleSrhToDBValue(txtsearch.Text).Replace("'", "''").Trim() + "%' ";
                    }
                    else
                    {
                        //sql = "select af_row_id as val," + bindfield + " as fld from " + tablename + " ";
                        sql = "select af_row_id as val," + bindfield + " from " + tablename + " ";
                    }
                    sql = applysecurity(sql);
                }
            }
        }
        catch (Exception ex)
        { LogWriter.WriteLog("Error GetQueryforMasterDetail"); }

        return sql;
    }

    private string applysecurity(string sql, string alias)
    {
        sql = applysecurity(sql);
        if (alias.Trim() != "")
            sql = sql.ToLower().Replace("owner_id", alias + ".owner_id");
        return sql;
    }

    private string applysecurity(string sql)
    {
        try
        {
            SecurityFilter objSec = new SecurityFilter();
            string sAFAdmin = objSec.QueryForAFAdmin();
            string sCustodianFilter = objSec.QueryForCustodianFilter();
            if (sCustodianFilter.Trim() != "")
                if (!sCustodianFilter.StartsWith("or"))
                    sAFAdmin += " and " + sCustodianFilter;
                else
                {
                    //if (QuickSearchSQL.Trim() == "")  // if no search exists show all no owner records
                    //{
                    sAFAdmin = "(" + sAFAdmin + " " + sCustodianFilter + ") ";
                    //sAFAdmin += " " + sCustodianFilter;
                    // }
                }

            if (sql.Contains("order by"))
            {
                if (sAFAdmin.Trim() != "")
                    sql = sql.Replace("order by", "where " + sAFAdmin + " order by");
            }
            else
            {
                if (sAFAdmin.Trim() != "")
                {
                    if (sql.Contains("where "))
                        sql = sql + " and (" + sAFAdmin + ")";
                    else
                        sql = sql + " where " + sAFAdmin;
                }
            }
        }
        catch
        { }
        return sql;
    }

    private DataSet GetPageControlCaptions(string strBindRowID)
    {
        DataSet ds = new DataSet();
        HDDatabase HDD = new HDDatabase();
        try
        {
            if (strBindRowID.Trim() != "")
            {
                string sql = "select B.* from sy_pg A, sy_pg_ctr B, sy_bnd C where A.af_row_id = B.page_id and A.page_table_name = C.bind_table and C.af_row_id = '" + strBindRowID + "'";
                ds = HDD.GetDataset(sql);
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : GetPageControlCaptions :" + ex.Message);
        }
        finally
        {
            HDD = null;
        }
        return ds;
    }

    private DataSet GetPageControlCaptionsFromPageset(string strPagesetRowId)
    {
        DataSet ds = new DataSet();
        HDDatabase HDD = new HDDatabase();
        try
        {
            if (strPagesetRowId.Trim() != "")
            {
                string sql = "select B.* from sy_pg A, sy_pg_ctr B, sy_pgs C, sy_pgs_mem D where A.af_row_id = B.page_id and A.page_id = D.page_id and C.af_row_id = D.pageset_id and C.af_row_id = '" + strPagesetRowId + "'";
                ds = HDD.GetDataset(sql);
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : GetPageControlCaptions :" + ex.Message);
        }
        finally
        {
            HDD = null;
        }
        return ds;
    }

    private void BuildGridForComposeEmail()
    {
        try
        {
            dsPgCtr = GetPageControlCaptionsFromPageset(Request.QueryString["pgs_id"].ToString());
            if (!Page.IsPostBack)
                if (Request.QueryString["SD"] != null)
                    txtsearch.Text = Request.QueryString["SD"].ToString();
            string emailfield = Request.QueryString["bnd"].ToString();
            string tablename = GetTableName(Request.QueryString["pgs_id"].ToString());
            DataRow[] drr = dsPgCtr.Tables[0].Select("control_visible = 1", "control_seq asc");
            string firstfield = "";
            if (drr.Length > 0)
                firstfield = drr[0]["control_id"].ToString();
            DataSet dsBind = GetDataSetForComposeEmail(emailfield, firstfield, tablename);
            GenerateGrid(dsBind, true, false);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : BuildGridForComposeEmail : " + ex.Message);
        }
    }

    private DataSet GetDataSetForComposeEmail(string email_field, string first_field, string tablename)
    {
        DataSet ds = new DataSet();
        HDDatabase HDD = new HDDatabase();
        DataSet dsPageInfo = new DataSet();
        PageInfoLoad PIL = null;
        try
        {
            string strstoragevalue = email_field;
            string strdisplayvalue = first_field;
            if (email_field.Trim() != "")
                if (first_field.Trim() != "")
                {
                    string emailfieldName = dsPgCtr.Tables[0].Select("control_id = '" + email_field + "'")[0]["control_name"].ToString();
                    string firstfieldName = dsPgCtr.Tables[0].Select("control_id = '" + first_field + "'")[0]["control_name"].ToString();
                    string sql = "";
                    PIL = new PageInfoLoad();
                    dsPageInfo = PIL.GetPageSetPageCtrlInfo(Request.QueryString["pgs_id"].ToString());

                    DataRow drPageKeys = null;
                    if (dsPageInfo != null)
                        if (dsPageInfo.Tables.Count > 0)
                            if (dsPageInfo.Tables["PageKeys"] != null)
                                if (dsPageInfo.Tables["PageKeys"].Rows.Count > 0)
                                    drPageKeys = dsPageInfo.Tables["PageKeys"].Rows[0];

                    if (drPageKeys != null)
                    {
                        string masterobj = "";
                        string display_att = "";
                        masterobj = drPageKeys["master_obj"].ToString();
                        display_att = drPageKeys["display_att"].ToString();
                        sql = "select object_table from sy_obj where object_id = '" + masterobj + "'";
                        string mastertable = HDD.GetColumnValue(sql);
                        //sql = "select " + email_field + " as Val, (select " + display_att + " from " + HDD.GetColumnValue(sql) + " where af_row_id = A." + first_field + ") as [" + firstfieldName + "]," + email_field + " as  [" + emailfieldName + "] from " + tablename + " A where " + email_field + " is not null";
                        sql = "select A." + email_field + " as Val,B." + display_att + " as [" + firstfieldName + "],A." + email_field + " as  [" + emailfieldName + "] from " + tablename + " A, " + mastertable + " B where (" + email_field + " is not null) and (A." + first_field + "=B.af_row_id)";
                        if (txtsearch.Text.Trim() != "")
                            sql = sql + " and B." + display_att + " like '%" + txtsearch.Text.Trim().Replace("'", "''") + "%'";

                        sql = applysecurity(sql, "A");
                        //}
                    }
                    else
                    {
                        sql = "select " + email_field + " as Val," + first_field + " as [" + firstfieldName + "]," + email_field + " as  [" + emailfieldName + "] from " + tablename + " where " + email_field + " is not null";
                        if (txtsearch.Text.Trim() != "")
                            sql = sql + " and " + first_field + " like '%" + txtsearch.Text.Trim().Replace("'", "''") + "%'";

                        sql = applysecurity(sql);
                    }
                    ds = HDD.GetDataset(sql);
                }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : GetDataSetForComposeEmail :" + ex.Message);
        }
        finally
        {
            PIL = null;
        }
        return ds;
    }
    private string GetTableName(string strPagesetRowId)
    {
        string result = "";
        HDDatabase HDD = null;
        try
        {
            if (strPagesetRowId.Trim() != "")
            {
                HDD = new HDDatabase();
                string sql = "select C.page_table_name from sy_pgs A, sy_pgs_mem B , sy_pg C where A.af_row_id = B.pageset_id and B.page_id = C.page_id and A.af_row_id = '" + strPagesetRowId + "'";
                result = HDD.GetColumnValue(sql);
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : GetTableName :" + ex.Message);
        }
        finally
        {
            HDD = null;
        }
        return result;
    }
    private string FirstFieldID(string strPagesetRowId)
    {
        string result = "";
        HDDatabase HDD = new HDDatabase();
        try
        {
            string sql = "select control_id from sy_pgs A, sy_pgs_mem B , sy_pg C,sy_pg_ctr D where A.af_row_id = B.pageset_id and B.page_id = C.page_id and C.af_row_id = D.page_id and D.control_visible =1 and A.af_row_id = '" + strPagesetRowId + "'  order by control_seq asc";
            result = HDD.GetColumnValue(sql);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : FirstFieldID :" + ex.Message);
        }
        finally
        {
            HDD = null;
        }
        return result;
    }

    private string GetParamterBindValue(string strBindRowID, string searchtext)
    {
        BindControl BC = new BindControl();
        HDDatabase HDD = new HDDatabase();
        string result = "";
        try
        {
            if (searchtext.Trim() != "")
            {
                string sql = BC.GetSearchSQLBySearchValue(strBindRowID, searchtext, true, false, "");
                DataTable dt = HDD.GetDataTable(sql);
                if (dt != null)
                    if (dt.Rows.Count > 0)
                        result = dt.Rows[0]["val"].ToString();
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("SearchPopup : GetParameterBindValue :" + ex.Message);
        }
        finally
        {
            BC = null;
            HDD = null;
        }
        return result;
    }

    /// <summary>
    /// To convert the DB values of datatable to localized values.
    /// </summary>
    /// <param name="dtBindObj">Datatable with values to be convert.</param>
    // Added by 'Imran Gani' on 28-Feb-2014, for MBLocale
    private void HandleControlDataType(DataTable dtBindObj)
    {
        HDDatabase HDD = new HDDatabase();
        Localization loc = new Localization();
        DataTable dtBindTemp = new DataTable();
        DataTable dtPgCtr = new DataTable();
        try
        {
            if (Request.QueryString["tbl"] != null && Request.QueryString["tbl"].ToString() != "")
            {
                dtBindTemp = dtBindObj.Clone();
                dtPgCtr = HDD.GetDataTable("select * from sy_pg_ctr where page_id = (select af_row_id from sy_pg where page_table_name = '" + Request.QueryString["tbl"].ToString() + "');");
                foreach (DataColumn dcBind in dtBindTemp.Columns)
                {
                    string sDisplayField = dcBind.ColumnName;
                    DataRow[] drCtrDetail = dtPgCtr.Select("control_id = '" + sDisplayField + "'");
                    if (drCtrDetail.Length > 0)
                        dtBindObj = loc.getLocalizedColumns(dtBindObj, drCtrDetail[0]["control_attrib_type"].ToString(), sDisplayField);
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("SearchPopup : HandleControlDataType :" + ex.Message);
        }
        finally
        {
            HDD = null;
            loc = null;
            dtBindTemp.Dispose();
            dtPgCtr.Dispose();
        }
    }

    /// <summary>
    /// To convert the locale value to DB values.
    /// </summary>
    /// <param name="sInput">Input string of localized value.</param>
    /// <returns>String DB formatted value.</returns>
    private string ConvertLocaleSrhToDBValue(string sInput)
    {
        HDDatabase HDD = new HDDatabase();
        Localization loc = new Localization();
        string sRet = "";
        try
        {
            if (Request.QueryString["tbl"] != null && Request.QueryString["tbl"].ToString() != "")
                if (Request.QueryString["fld"] != null && Request.QueryString["fld"].ToString() != "")
                {
                    DataRow drCtrDetail = HDD.GetTableRow("select * from sy_pg_ctr where page_id = (select af_row_id from sy_pg where page_table_name = '" + Request.QueryString["tbl"].ToString() + "') and control_id = '" + Request.QueryString["fld"].ToString() + "';");
                    sRet = loc.getDBValueFromLocale(sInput, drCtrDetail["control_attrib_type"].ToString());
                }
            return sRet;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("SearchPopup : ConvertLocaleSrhToDBValue :" + ex.Message);
            return sInput;
        }
        finally
        {
            HDD = null;
            loc = null;
        }
    }
}
